OML4SQL Clustering EM.dsnb

[{"layout":null,"template":null,"templateConfig":null,"name":"OML4SQL Clustering EM","description":null,"readOnly":false,"type":"low","paragraphs":[{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":null,"message":[],"enabled":true,"result":{"startTime":1715349091630,"interpreter":"md.low","endTime":1715349091707,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","# Identifying Customer Segments using Expectation Maximization Clustering","","Oracle Machine Learning supports clustering using several algorithms, including k-Means, O-Cluster, and Expectation Maximization. In this notebook, we illustrate how to identify natural clusters of customers using the CUSTOMERS dataset from the SH schema using the unsupervised learning Expectation Maximization algorithm. The data exploration, preparation, and machine learning runs inside Oracle Autonomous Database.","","See the documentation link below for details on the Expectation Maximization in-database algortihm.","","Copyright (c) 2024 Oracle Corporation ","###### <a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a>","---"],"enabled":true,"result":{"startTime":1715349091783,"interpreter":"md.low","endTime":1715349091864,"results":[{"message":"<h1 id=\"identifying-customer-segments-using-expectation-maximization-clustering\">Identifying Customer Segments using Expectation Maximization Clustering<\/h1>\n<p>Oracle Machine Learning supports clustering using several algorithms, including k-Means, O-Cluster, and Expectation Maximization. In this notebook, we illustrate how to identify natural clusters of customers using the CUSTOMERS dataset from the SH schema using the unsupervised learning Expectation Maximization algorithm. The data exploration, preparation, and machine learning runs inside Oracle Autonomous Database.<\/p>\n<p>See the documentation link below for details on the Expectation Maximization in-database algortihm.<\/p>\n<p>Copyright (c) 2024 Oracle Corporation<\/p>\n<h6 id=\"the-universal-permissive-license-upl-version-10\"><a href=\"https://oss.oracle.com/licenses/upl/\" onclick=\"return ! window.open('https://oss.oracle.com/licenses/upl/');\">The Universal Permissive License (UPL), Version 1.0<\/a><\/h6>\n<hr />\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":9,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","![tiny arrow](http://www.oracle.com/technetwork/database/options/advanced-analytics/clustering-5663171.jpg \"tiny arrow\")"],"enabled":true,"result":{"startTime":1715349091948,"interpreter":"md.low","endTime":1715349092024,"results":[{"message":"<p><img src=\"http://www.oracle.com/technetwork/database/options/advanced-analytics/clustering-5663171.jpg\" alt=\"tiny arrow\" title=\"tiny arrow\" /><\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":3,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"For more information...","message":["%md ","","* <a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a>","* <a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a>","* <a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a>","* <a href=\"https://www.oracle.com/goto/ml-clustering\" target=\"_blank\">OML Clustering<\/a>","* <a href=\"https://oracle.com/goto/ml-expectation-maximization\" target=\"_blank\">OML Expectation Maximization<\/a>"],"enabled":true,"result":{"startTime":1715349092101,"interpreter":"md.low","endTime":1715349092162,"results":[{"message":"<ul>\n<li><a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/index.html\" target=\"_blank\">Oracle ADB Documentation<\/a><\/li>\n<li><a href=\"https://github.com/oracle-samples/oracle-db-examples/tree/main/machine-learning\" target=\"_blank\">OML folder on Oracle GitHub<\/a><\/li>\n<li><a href=\"https://www.oracle.com/machine-learning\" target=\"_blank\">OML Web Page<\/a><\/li>\n<li><a href=\"https://www.oracle.com/goto/ml-clustering\" target=\"_blank\">OML Clustering<\/a><\/li>\n<li><a href=\"https://oracle.com/goto/ml-expectation-maximization\" target=\"_blank\">OML Expectation Maximization<\/a><\/li>\n<\/ul>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create view joining CUSTOMERS and DEMOGRAPHICS","message":["%script","","CREATE OR REPLACE VIEW CUSTOMERS360_V AS"," SELECT a.CUST_ID, a.CUST_GENDER, a.CUST_MARITAL_STATUS, "," a.CUST_YEAR_OF_BIRTH, a.CUST_INCOME_LEVEL, a.CUST_CREDIT_LIMIT, "," b.EDUCATION, b.AFFINITY_CARD, "," b.HOUSEHOLD_SIZE, b.OCCUPATION, b.YRS_RESIDENCE, b.Y_BOX_GAMES"," FROM SH.CUSTOMERS a, SH.SUPPLEMENTARY_DEMOGRAPHICS b"," WHERE a.CUST_ID = b.CUST_ID;"," "],"enabled":true,"result":{"startTime":1715349092239,"interpreter":"script.low","endTime":1715349094138,"results":[{"message":"\nView CUSTOMERS360_V created.\n\n\n---------------------------\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Count number of records in CUSTOMERS360_V","message":["%sql","","SELECT COUNT(*) COUNT","FROM CUSTOMERS360_V;"],"enabled":true,"result":{"startTime":1715349094217,"interpreter":"sql.low","endTime":1715349094368,"results":[{"message":"COUNT\n4500\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display data in view CUSTOMERS360_V","message":["%sql","","SELECT * ","FROM CUSTOMERS360_V","FETCH FIRST 10 ROWS ONLY;"],"enabled":true,"result":{"startTime":1715349094449,"interpreter":"sql.low","endTime":1715349094569,"results":[{"message":"CUST_ID\tCUST_GENDER\tCUST_MARITAL_STATUS\tCUST_YEAR_OF_BIRTH\tCUST_INCOME_LEVEL\tCUST_CREDIT_LIMIT\tEDUCATION\tAFFINITY_CARD\tHOUSEHOLD_SIZE\tOCCUPATION\tYRS_RESIDENCE\tY_BOX_GAMES\n100134\tF\tDivorc.\t1965\tL: 300,000 and above\t9000\tAssoc-A\t0\t2\tCleric.\t2\t0\n102828\tF\tNeverM\t1967\tE: 90,000 - 109,999\t10000\tHS-grad\t0\t1\tMachine\t4\t0\n101232\tM\tNeverM\t1979\tJ: 190,000 - 249,999\t9000\t< Bach.\t0\t1\tOther\t2\t1\n100696\tM\tMarried\t1971\tF: 110,000 - 129,999\t7000\tProfsc\t1\t3\tProf.\t3\t0\n103948\tM\tNeverM\t1966\tJ: 190,000 - 249,999\t9000\t< Bach.\t0\t1\tCleric.\t4\t0\n103791\tM\tDivorc.\t1952\tB: 30,000 - 49,999\t3000\tHS-grad\t0\t2\tProf.\t5\t0\n100804\tF\tDivorc.\t1943\tA: Below 30,000\t1500\t< Bach.\t0\t2\tProf.\t6\t0\n101610\tM\tNeverM\t1985\tI: 170,000 - 189,999\t3000\t11th\t0\t1\tHandler\t0\t1\n102308\tM\tNeverM\t1980\tJ: 190,000 - 249,999\t11000\t< Bach.\t0\t2\tProf.\t2\t1\n100593\tM\tMarried\t1963\tG: 130,000 - 149,999\t1500\tHS-grad\t1\t3\tProf.\t4\t0\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Build an Expectation Maximization model using the CUSTOMERS360_V view"],"enabled":true,"result":{"startTime":1715349094651,"interpreter":"md.low","endTime":1715349094713,"results":[{"message":"<h3 id=\"build-an-expectation-maximization-model-using-the-customers360_v-view\">Build an Expectation Maximization model using the CUSTOMERS360_V view<\/h3>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build the EM Clustering model with default settings","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('EM_SH_CLUS_SAMPLE_1');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;","BEGIN"," v_setlst('ALGO_NAME') := 'ALGO_EXPECTATION_MAXIMIZATION';",""," DBMS_DATA_MINING.CREATE_MODEL2("," MODEL_NAME => 'EM_SH_CLUS_SAMPLE_1',"," MINING_FUNCTION => 'CLUSTERING',"," DATA_QUERY => 'select * from CUSTOMERS360_V',"," SET_LIST => v_setlst,"," CASE_ID_COLUMN_NAME => 'CUST_ID');","END;"],"enabled":true,"result":{"startTime":1715349094796,"interpreter":"script.low","endTime":1715349100801,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Examples of possible setting overrides for Expectation-Maximization clustering","","If the user does not override the default settings, then relevant settings are determined by the algorithm.","","A complete list of settings can be found in the Documentation link:","","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-1796B451-BE1B-43BC-9839-05F5F73031C8\" target=\"_blank\">Algorithm Settings<\/a>","* <a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" target=\"_blank\">Shared Settings<\/a>","","* Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is `ODMS_MISSING_VALUE_AUTO`. The option `ODMS_MISSING_VALUE_MEAN_MODE` replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option `ODMS_MISSING_VALUE_AUTO` performs different strategies for different algorithms. When `ODMS_MISSING_VALUE_TREATMENT` is set to `ODMS_MISSING_VALUE_DELETE_ROW`, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.","> v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO'; ","* Specify the maximum number of leaf clusters generated by a clustering algorithm. The algorithm may return fewer clusters, depending on the data. Expectation maximization (EM) may return fewer clusters than the number specified by CLUS_NUM_CLUSTERS depending on the data. The number of clusters returned by EM cannot be greater than the number of components, which is governed by algorithm-specific settings (see Expectation Maximization Settings for Learning table). Depending on these settings, there may be fewer clusters than components. If component clustering is disabled, the number of clusters equals the number of components. For EM, the default value of CLUS_NUM_CLUSTERS is system-determined. For k-Means and O-Cluster, the default is 10. ","> v_setlst('CLUS_NUM_CLUSTERS') := '5';","","#### Expectation Maximization Settings for Learning ","* Specify this setting to enable model search in EM where different model sizes are explored and a best size is selected. It requires `EMCS_MODEL_SEARCH_ENABLE` or `EMCS_MODEL_SEARCH_DISABLE`. The default is `EMCS_MODEL_SEARCH_DISABLE`. ","> v_setlst('EMCS_MODEL_SEARCH') := 'EMCS_MODEL_SEARCH_ENABLE';","* Specify the maximum number of iterations in the EM algorithm. It requires a number larger than 1. The default is 100. ","> v_setlst('EMCS_NUM_ITERATIONS') := '20';"," ","#### Expectation Maximization Settings for Cluster Statistics","* Specify whether to enable or disable the gathering of descriptive statistics for clusters (centroids, histograms, and rules). When statistics are disabled, model size is reduced, and `GET_MODEL_DETAILS_EM` only returns taxonomy (hierarchy) and cluster counts. It requires either `EMCS_CLUS_STATS_ENABLE` or `EMCS_CLUS_STATS_DISABLE`. Default is `EMCS_CLUS_STATS_ENABLE`. ","> v_setlst('EMCS_CLUSTER_STATISTICS') := 'EMCS_CLUS_STATS_ENABLE';"," ","#### Expectation Maximization Settings for Data Preparation and Analysis","* Specify the number of quantile bins that will be used for modeling numeric columns with multivalued Bernoulli distributions. It requires a number larger than 1 and up to 255 (inclusive). The default is system-determined.","> v_setlst('EMCS_NUM_QUANTILE_BINS') := '10';"],"enabled":true,"result":{"startTime":1715349116891,"interpreter":"md.low","endTime":1715349116983,"results":[{"message":"<h3 id=\"examples-of-possible-setting-overrides-for-expectation-maximization-clustering\">Examples of possible setting overrides for Expectation-Maximization clustering<\/h3>\n<p>If the user does not override the default settings, then relevant settings are determined by the algorithm.<\/p>\n<p>A complete list of settings can be found in the Documentation link:<\/p>\n<ul>\n<li>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-1796B451-BE1B-43BC-9839-05F5F73031C8\" target=\"_blank\">Algorithm Settings<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DATA_MINING.html#GUID-24047A09-0542-4870-91D8-329F28B0ED75\" target=\"_blank\">Shared Settings<\/a><\/p>\n<\/li>\n<li>\n<p>Specify a missing value treatment method for the training data. This setting does not affect the scoring data. The default value is <code>ODMS_MISSING_VALUE_AUTO<\/code>. The option <code>ODMS_MISSING_VALUE_MEAN_MODE<\/code> replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time where appropriate. The option <code>ODMS_MISSING_VALUE_AUTO<\/code> performs different strategies for different algorithms. When <code>ODMS_MISSING_VALUE_TREATMENT<\/code> is set to <code>ODMS_MISSING_VALUE_DELETE_ROW<\/code>, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, then you must perform the transformation explicitly.<\/p>\n<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('ODMS_MISSING_VALUE_TREATMENT') := 'ODMS_MISSING_VALUE_AUTO';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the maximum number of leaf clusters generated by a clustering algorithm. The algorithm may return fewer clusters, depending on the data. Expectation maximization (EM) may return fewer clusters than the number specified by CLUS_NUM_CLUSTERS depending on the data. The number of clusters returned by EM cannot be greater than the number of components, which is governed by algorithm-specific settings (see Expectation Maximization Settings for Learning table). Depending on these settings, there may be fewer clusters than components. If component clustering is disabled, the number of clusters equals the number of components. For EM, the default value of CLUS_NUM_CLUSTERS is system-determined. For k-Means and O-Cluster, the default is 10.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('CLUS_NUM_CLUSTERS') := '5';<\/p>\n<\/blockquote>\n<h4 id=\"expectation-maximization-settings-for-learning\">Expectation Maximization Settings for Learning<\/h4>\n<ul>\n<li>Specify this setting to enable model search in EM where different model sizes are explored and a best size is selected. It requires <code>EMCS_MODEL_SEARCH_ENABLE<\/code> or <code>EMCS_MODEL_SEARCH_DISABLE<\/code>. The default is <code>EMCS_MODEL_SEARCH_DISABLE<\/code>.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('EMCS_MODEL_SEARCH') := 'EMCS_MODEL_SEARCH_ENABLE';<\/p>\n<\/blockquote>\n<ul>\n<li>Specify the maximum number of iterations in the EM algorithm. It requires a number larger than 1. The default is 100.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('EMCS_NUM_ITERATIONS') := '20';<\/p>\n<\/blockquote>\n<h4 id=\"expectation-maximization-settings-for-cluster-statistics\">Expectation Maximization Settings for Cluster Statistics<\/h4>\n<ul>\n<li>Specify whether to enable or disable the gathering of descriptive statistics for clusters (centroids, histograms, and rules). When statistics are disabled, model size is reduced, and <code>GET_MODEL_DETAILS_EM<\/code> only returns taxonomy (hierarchy) and cluster counts. It requires either <code>EMCS_CLUS_STATS_ENABLE<\/code> or <code>EMCS_CLUS_STATS_DISABLE<\/code>. Default is <code>EMCS_CLUS_STATS_ENABLE<\/code>.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('EMCS_CLUSTER_STATISTICS') := 'EMCS_CLUS_STATS_ENABLE';<\/p>\n<\/blockquote>\n<h4 id=\"expectation-maximization-settings-for-data-preparation-and-analysis\">Expectation Maximization Settings for Data Preparation and Analysis<\/h4>\n<ul>\n<li>Specify the number of quantile bins that will be used for modeling numeric columns with multivalued Bernoulli distributions. It requires a number larger than 1 and up to 255 (inclusive). The default is system-determined.<\/li>\n<\/ul>\n<blockquote>\n<p>v_setlst('EMCS_NUM_QUANTILE_BINS') := '10';<\/p>\n<\/blockquote>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Build the EM Clustering model with explicit settings","message":["%script","","-- Additional settings are available in the documentation","","BEGIN DBMS_DATA_MINING.DROP_MODEL('EM_SH_CLUS_SAMPLE_2');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","DECLARE"," v_setlst DBMS_DATA_MINING.SETTING_LIST;","BEGIN"," v_setlst('ALGO_NAME') := 'ALGO_EXPECTATION_MAXIMIZATION';"," v_setlst('PREP_AUTO') := 'ON';"," v_setlst('CLUS_NUM_CLUSTERS') := '5';"," v_setlst('EMCS_MODEL_SEARCH') := 'EMCS_MODEL_SEARCH_ENABLE';"," v_setlst('EMCS_CLUSTER_STATISTICS') := 'EMCS_CLUS_STATS_ENABLE';"," v_setlst('EMCS_NUM_ITERATIONS') := '20';"," v_setlst('EMCS_NUM_QUANTILE_BINS') := '10';"," v_setlst('EMCS_RANDOM_SEED') := '123456';",""," DBMS_DATA_MINING.CREATE_MODEL2("," MODEL_NAME => 'EM_SH_CLUS_SAMPLE_2',"," MINING_FUNCTION => 'CLUSTERING',"," DATA_QUERY => 'select * from CUSTOMERS360_V',"," SET_LIST => v_setlst,"," CASE_ID_COLUMN_NAME => 'CUST_ID');","END;"],"enabled":true,"result":{"startTime":1715349101049,"interpreter":"script.low","endTime":1715349105551,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":5,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the model metadata","message":["%sql ","","SELECT MODEL_NAME, MINING_FUNCTION, ALGORITHM, CREATION_DATE, PARTITIONED","FROM USER_MINING_MODELS","WHERE MODEL_NAME LIKE 'EM_SH_CLUS_SAMPLE_%';"],"enabled":true,"result":{"startTime":1715349105636,"interpreter":"sql.low","endTime":1715349105737,"results":[{"message":"MODEL_NAME\tMINING_FUNCTION\tALGORITHM\tCREATION_DATE\tPARTITIONED\nEM_SH_CLUS_SAMPLE_3\tCLUSTERING\tEXPECTATION_MAXIMIZATION\t2024-04-22 16:21:13\tNO\nEM_SH_CLUS_SAMPLE_2\tCLUSTERING\tEXPECTATION_MAXIMIZATION\t2024-05-10 13:51:42\tNO\nEM_SH_CLUS_SAMPLE_1\tCLUSTERING\tEXPECTATION_MAXIMIZATION\t2024-05-10 13:51:36\tNO\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":7,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the model signature","message":["%sql ","","-- The model signature includes the attributes used in the model training data,","-- and also corresponds to the attributes expected to apply the model to new data. ","","SELECT ATTRIBUTE_NAME, ATTRIBUTE_TYPE","FROM USER_MINING_MODEL_ATTRIBUTES","WHERE MODEL_NAME = 'EM_SH_CLUS_SAMPLE_2'","ORDER BY ATTRIBUTE_NAME;"],"enabled":true,"result":{"startTime":1715349105819,"interpreter":"sql.low","endTime":1715349105896,"results":[{"message":"ATTRIBUTE_NAME\tATTRIBUTE_TYPE\nAFFINITY_CARD\tNUMERICAL\nCUST_CREDIT_LIMIT\tNUMERICAL\nCUST_GENDER\tCATEGORICAL\nCUST_INCOME_LEVEL\tCATEGORICAL\nCUST_MARITAL_STATUS\tCATEGORICAL\nCUST_YEAR_OF_BIRTH\tNUMERICAL\nEDUCATION\tCATEGORICAL\nHOUSEHOLD_SIZE\tCATEGORICAL\nOCCUPATION\tCATEGORICAL\nYRS_RESIDENCE\tNUMERICAL\nY_BOX_GAMES\tNUMERICAL\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the actual settings used by the algorithm","message":["%sql","","SELECT SETTING_NAME, SETTING_VALUE","FROM USER_MINING_MODEL_SETTINGS","WHERE MODEL_NAME = 'EM_SH_CLUS_SAMPLE_2'","ORDER BY SETTING_NAME;"],"enabled":true,"result":{"startTime":1715349105975,"interpreter":"sql.low","endTime":1715349106073,"results":[{"message":"SETTING_NAME\tSETTING_VALUE\nALGO_NAME\tALGO_EXPECTATION_MAXIMIZATION\nCLUS_NUM_CLUSTERS\t5\nEMCS_CLUSTER_COMPONENTS\tEMCS_CLUSTER_COMP_ENABLE\nEMCS_CLUSTER_STATISTICS\tEMCS_CLUS_STATS_ENABLE\nEMCS_CLUSTER_THRESH\t2\nEMCS_LINKAGE_FUNCTION\tEMCS_LINKAGE_SINGLE\nEMCS_LOGLIKE_IMPROVEMENT\t.001\nEMCS_MAX_NUM_ATTR_2D\t50\nEMCS_MIN_PCT_ATTR_SUPPORT\t.1\nEMCS_MODEL_SEARCH\tEMCS_MODEL_SEARCH_ENABLE\nEMCS_NUM_COMPONENTS\t20\nEMCS_NUM_DISTRIBUTION\tEMCS_NUM_DISTR_SYSTEM\nEMCS_NUM_EQUIWIDTH_BINS\t11\nEMCS_NUM_ITERATIONS\t20\nEMCS_NUM_PROJECTIONS\t50\nEMCS_NUM_QUANTILE_BINS\t10\nEMCS_RANDOM_SEED\t123456\nEMCS_REMOVE_COMPONENTS\tEMCS_REMOVE_COMPS_ENABLE\nODMS_DETAILS\tODMS_ENABLE\nODMS_MISSING_VALUE_TREATMENT\tODMS_MISSING_VALUE_AUTO\nODMS_SAMPLING\tODMS_SAMPLING_DISABLE\nPREP_AUTO\tON\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the EM model detail views","message":["%sql ","","-- Display the list of available cluster model detail views as indicated in VIEW_TYPE","-- Cluster details are presented as separate views depending on the desired information.","","SELECT VIEW_NAME, VIEW_TYPE ","FROM USER_MINING_MODEL_VIEWS","WHERE MODEL_NAME='EM_SH_CLUS_SAMPLE_2'","ORDER BY VIEW_NAME;"],"enabled":true,"result":{"startTime":1715349106162,"interpreter":"sql.low","endTime":1715349106296,"results":[{"message":"VIEW_NAME\tVIEW_TYPE\nDM$VAEM_SH_CLUS_SAMPLE_2\tClustering Attribute Statistics\nDM$VBEM_SH_CLUS_SAMPLE_2\tAttribute Pair Kullback-Leibler Divergence\nDM$VDEM_SH_CLUS_SAMPLE_2\tClustering Description\nDM$VFEM_SH_CLUS_SAMPLE_2\tExpectation Maximization Bernoulli parameters\nDM$VGEM_SH_CLUS_SAMPLE_2\tGlobal Name-Value Pairs\nDM$VHEM_SH_CLUS_SAMPLE_2\tClustering Histograms\nDM$VIEM_SH_CLUS_SAMPLE_2\tUnsupervised Attribute Importance\nDM$VMEM_SH_CLUS_SAMPLE_2\tExpectation Maximization Gaussian parameters\nDM$VNEM_SH_CLUS_SAMPLE_2\tNormalization and Missing Value Handling\nDM$VOEM_SH_CLUS_SAMPLE_2\tExpectation Maximization Components\nDM$VPEM_SH_CLUS_SAMPLE_2\tExpectation Maximization Projections\nDM$VREM_SH_CLUS_SAMPLE_2\tClustering Rules\nDM$VSEM_SH_CLUS_SAMPLE_2\tComputed Settings\nDM$VWEM_SH_CLUS_SAMPLE_2\tModel Build Alerts\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Show Cluster details","message":["%sql","","-- For each cluster_id, this DM$VD* view provides the number of records in each cluster,","-- the parent cluster id, and the level in the hierarchy. ","","SELECT CLUSTER_ID, RECORD_COUNT, PARENT, TREE_LEVEL","FROM DM$VDEM_SH_CLUS_SAMPLE_2","ORDER BY CLUSTER_ID;"],"enabled":true,"result":{"startTime":1715349106388,"interpreter":"sql.low","endTime":1715349106510,"results":[{"message":"CLUSTER_ID\tRECORD_COUNT\tPARENT\tTREE_LEVEL\n1\t4500\t\t1\n2\t4310\t1\t2\n3\t190\t1\t2\n4\t2725\t2\t3\n5\t1585\t2\t3\n6\t1322\t4\t4\n7\t1403\t4\t4\n8\t1170\t7\t5\n9\t233\t7\t5\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Show leaf clusters IDs","message":["%sql","","SELECT CLUSTER_ID","FROM DM$VDEM_SH_CLUS_SAMPLE_2","WHERE LEFT_CHILD_ID IS NULL AND RIGHT_CHILD_ID IS NULL","ORDER BY CLUSTER_ID"],"enabled":true,"result":{"startTime":1715349106592,"interpreter":"sql.low","endTime":1715349106688,"results":[{"message":"CLUSTER_ID\n3\n5\n6\n8\n9\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Show Cluster details for cluster 9","message":["%sql","","-- For cluster_id 9, this output lists all the attributes statistics that constitute the centroid, with the mean and variance (for numericals) ","-- or mode (for categoricals).","","SELECT CLUSTER_ID, ATTRIBUTE_NAME, ATTRIBUTE_SUBNAME, ROUND(MEAN,3) MEAN, ROUND(VARIANCE,3) VARIANCE, MODE_VALUE","FROM DM$VAEM_SH_CLUS_SAMPLE_2","WHERE CLUSTER_ID = 9","ORDER BY ATTRIBUTE_NAME, ATTRIBUTE_SUBNAME;"],"enabled":true,"result":{"startTime":1715349106769,"interpreter":"sql.low","endTime":1715349106871,"results":[{"message":"CLUSTER_ID\tATTRIBUTE_NAME\tATTRIBUTE_SUBNAME\tMEAN\tVARIANCE\tMODE_VALUE\n9\tAFFINITY_CARD\t\t0.167\t0.14\t\n9\tCUST_CREDIT_LIMIT\t\t8291.845\t16218338.39\t\n9\tCUST_GENDER\t\t\t\tM\n9\tCUST_INCOME_LEVEL\t\t\t\tJ: 190,000 - 249,999\n9\tCUST_MARITAL_STATUS\t\t\t\tMarried\n9\tCUST_YEAR_OF_BIRTH\t\t1976.506\t4.941\t\n9\tEDUCATION\t\t\t\tHS-grad\n9\tHOUSEHOLD_SIZE\t\t\t\t3\n9\tOCCUPATION\t\t\t\tCrafts\n9\tYRS_RESIDENCE\t\t2.987\t0.659\t\n9\tY_BOX_GAMES\t\t1\t0\t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"bar\":{\"groupByColumns\":[\"BIN_ID\",\"ATTRIBUTE_VALUE\"],\"showSeries\":[\"COUNT\"],\"aggregationOption\":\"Last\",\"series\":{\"availableSeriesElements\":[{\"id\":\"COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"CLUSTER_ID\",\"ATTRIBUTE_NAME\",\"BIN_ID\",\"COUNT\",\"LOWER_BIN_BOUNDARY\",\"UPPER_BIN_BOUNDARY\",\"ATTRIBUTE_VALUE\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Show histogram for attribute YRS_RESIDENCE of cluster 6","message":["%sql","","-- Use the DM$VH view for cluster histogram data. ","","SELECT CLUSTER_ID, ATTRIBUTE_NAME, "," BIN_ID, COUNT, LOWER_BIN_BOUNDARY, UPPER_BIN_BOUNDARY, ATTRIBUTE_VALUE","FROM DM$VHEM_SH_CLUS_SAMPLE_2","WHERE CLUSTER_ID = 6 AND ATTRIBUTE_NAME = 'YRS_RESIDENCE'","ORDER BY BIN_ID;"],"enabled":true,"result":{"startTime":1715349106957,"interpreter":"sql.low","endTime":1715349107078,"results":[{"message":"CLUSTER_ID\tATTRIBUTE_NAME\tBIN_ID\tCOUNT\tLOWER_BIN_BOUNDARY\tUPPER_BIN_BOUNDARY\tATTRIBUTE_VALUE\n6\tYRS_RESIDENCE\t1\t17\t0.0\t1.0\t\n6\tYRS_RESIDENCE\t2\t71\t1.0\t2.0\t\n6\tYRS_RESIDENCE\t3\t235\t2.0\t3.0\t\n6\tYRS_RESIDENCE\t4\t383\t3.0\t4.0\t\n6\tYRS_RESIDENCE\t5\t308\t4.0\t5.0\t\n6\tYRS_RESIDENCE\t6\t176\t5.0\t6.0\t\n6\tYRS_RESIDENCE\t7\t72\t6.0\t7.0\t\n6\tYRS_RESIDENCE\t8\t30\t7.0\t8.0\t\n6\tYRS_RESIDENCE\t9\t30\t8.0\t14.0\t\n6\tYRS_RESIDENCE\t10\t0\t\t\t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":"[{\"bar\":{\"groupByColumns\":[\"BIN_ID\"],\"showSeries\":[\"COUNT\"],\"series\":{\"availableSeriesElements\":[{\"id\":\"COUNT\",\"lineType\":\"straight\",\"borderColor\":\"rgb(25, 95, 116)\",\"borderWidth\":0,\"color\":\"rgb(25, 95, 116)\",\"pattern\":\"auto\",\"markerColor\":\"rgb(25, 95, 116)\",\"markerDisplayed\":\"auto\",\"markerShape\":\"auto\",\"markerSize\":0}]},\"lastColumns\":[\"CLUSTER_ID\",\"ATTRIBUTE_NAME\",\"BIN_ID\",\"COUNT\",\"LOWER_BIN_BOUNDARY\",\"UPPER_BIN_BOUNDARY\",\"ATTRIBUTE_VALUE\"],\"version\":1}}]","hideInIFrame":false,"selectedVisualization":"bar","title":"Show histogram for attribute YRS_RESIDENCE of cluster 9","message":["%sql","","-- Use the DM$VH view for cluster histogram data. ","","SELECT CLUSTER_ID, ATTRIBUTE_NAME, "," BIN_ID, COUNT, LOWER_BIN_BOUNDARY, UPPER_BIN_BOUNDARY, ATTRIBUTE_VALUE ","FROM DM$VHEM_SH_CLUS_SAMPLE_2","WHERE CLUSTER_ID = 9 AND ATTRIBUTE_NAME = 'YRS_RESIDENCE'","ORDER BY BIN_ID;"],"enabled":true,"result":{"startTime":1715349107166,"interpreter":"sql.low","endTime":1715349107264,"results":[{"message":"CLUSTER_ID\tATTRIBUTE_NAME\tBIN_ID\tCOUNT\tLOWER_BIN_BOUNDARY\tUPPER_BIN_BOUNDARY\tATTRIBUTE_VALUE\n9\tYRS_RESIDENCE\t1\t7\t0.0\t1.0\t\n9\tYRS_RESIDENCE\t2\t41\t1.0\t2.0\t\n9\tYRS_RESIDENCE\t3\t145\t2.0\t3.0\t\n9\tYRS_RESIDENCE\t4\t32\t3.0\t4.0\t\n9\tYRS_RESIDENCE\t5\t4\t4.0\t5.0\t\n9\tYRS_RESIDENCE\t6\t4\t5.0\t6.0\t\n9\tYRS_RESIDENCE\t7\t0\t6.0\t7.0\t\n9\tYRS_RESIDENCE\t8\t0\t7.0\t8.0\t\n9\tYRS_RESIDENCE\t9\t0\t8.0\t14.0\t\n9\tYRS_RESIDENCE\t10\t0\t\t\t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"View bin boundaries for YRS_RESIDENCE","message":["%sql","","SELECT CLUSTER_ID, ATTRIBUTE_NAME, BIN_ID, LOWER_BIN_BOUNDARY, UPPER_BIN_BOUNDARY, COUNT ","FROM DM$VHEM_SH_CLUS_SAMPLE_2","WHERE ATTRIBUTE_NAME = 'YRS_RESIDENCE'","ORDER BY CLUSTER_ID DESC, BIN_ID;"],"enabled":true,"result":{"startTime":1715349107350,"interpreter":"sql.low","endTime":1715349107448,"results":[{"message":"CLUSTER_ID\tATTRIBUTE_NAME\tBIN_ID\tLOWER_BIN_BOUNDARY\tUPPER_BIN_BOUNDARY\tCOUNT\n9\tYRS_RESIDENCE\t1\t0.0\t1.0\t7\n9\tYRS_RESIDENCE\t2\t1.0\t2.0\t41\n9\tYRS_RESIDENCE\t3\t2.0\t3.0\t145\n9\tYRS_RESIDENCE\t4\t3.0\t4.0\t32\n9\tYRS_RESIDENCE\t5\t4.0\t5.0\t4\n9\tYRS_RESIDENCE\t6\t5.0\t6.0\t4\n9\tYRS_RESIDENCE\t7\t6.0\t7.0\t0\n9\tYRS_RESIDENCE\t8\t7.0\t8.0\t0\n9\tYRS_RESIDENCE\t9\t8.0\t14.0\t0\n9\tYRS_RESIDENCE\t10\t\t\t0\n8\tYRS_RESIDENCE\t1\t0.0\t1.0\t282\n8\tYRS_RESIDENCE\t2\t1.0\t2.0\t468\n8\tYRS_RESIDENCE\t3\t2.0\t3.0\t363\n8\tYRS_RESIDENCE\t4\t3.0\t4.0\t46\n8\tYRS_RESIDENCE\t5\t4.0\t5.0\t10\n8\tYRS_RESIDENCE\t6\t5.0\t6.0\t1\n8\tYRS_RESIDENCE\t7\t6.0\t7.0\t0\n8\tYRS_RESIDENCE\t8\t7.0\t8.0\t0\n8\tYRS_RESIDENCE\t9\t8.0\t14.0\t0\n8\tYRS_RESIDENCE\t10\t\t\t0\n7\tYRS_RESIDENCE\t1\t0.0\t1.0\t289\n7\tYRS_RESIDENCE\t2\t1.0\t2.0\t509\n7\tYRS_RESIDENCE\t3\t2.0\t3.0\t508\n7\tYRS_RESIDENCE\t4\t3.0\t4.0\t78\n7\tYRS_RESIDENCE\t5\t4.0\t5.0\t14\n7\tYRS_RESIDENCE\t6\t5.0\t6.0\t5\n7\tYRS_RESIDENCE\t7\t6.0\t7.0\t0\n7\tYRS_RESIDENCE\t8\t7.0\t8.0\t0\n7\tYRS_RESIDENCE\t9\t8.0\t14.0\t0\n7\tYRS_RESIDENCE\t10\t\t\t0\n6\tYRS_RESIDENCE\t1\t0.0\t1.0\t17\n6\tYRS_RESIDENCE\t2\t1.0\t2.0\t71\n6\tYRS_RESIDENCE\t3\t2.0\t3.0\t235\n6\tYRS_RESIDENCE\t4\t3.0\t4.0\t383\n6\tYRS_RESIDENCE\t5\t4.0\t5.0\t308\n6\tYRS_RESIDENCE\t6\t5.0\t6.0\t176\n6\tYRS_RESIDENCE\t7\t6.0\t7.0\t72\n6\tYRS_RESIDENCE\t8\t7.0\t8.0\t30\n6\tYRS_RESIDENCE\t9\t8.0\t14.0\t30\n6\tYRS_RESIDENCE\t10\t\t\t0\n5\tYRS_RESIDENCE\t1\t0.0\t1.0\t11\n5\tYRS_RESIDENCE\t2\t1.0\t2.0\t28\n5\tYRS_RESIDENCE\t3\t2.0\t3.0\t170\n5\tYRS_RESIDENCE\t4\t3.0\t4.0\t418\n5\tYRS_RESIDENCE\t5\t4.0\t5.0\t396\n5\tYRS_RESIDENCE\t6\t5.0\t6.0\t284\n5\tYRS_RESIDENCE\t7\t6.0\t7.0\t151\n5\tYRS_RESIDENCE\t8\t7.0\t8.0\t67\n5\tYRS_RESIDENCE\t9\t8.0\t14.0\t60\n5\tYRS_RESIDENCE\t10\t\t\t0\n4\tYRS_RESIDENCE\t1\t0.0\t1.0\t306\n4\tYRS_RESIDENCE\t2\t1.0\t2.0\t580\n4\tYRS_RESIDENCE\t3\t2.0\t3.0\t743\n4\tYRS_RESIDENCE\t4\t3.0\t4.0\t461\n4\tYRS_RESIDENCE\t5\t4.0\t5.0\t322\n4\tYRS_RESIDENCE\t6\t5.0\t6.0\t181\n4\tYRS_RESIDENCE\t7\t6.0\t7.0\t72\n4\tYRS_RESIDENCE\t8\t7.0\t8.0\t30\n4\tYRS_RESIDENCE\t9\t8.0\t14.0\t30\n4\tYRS_RESIDENCE\t10\t\t\t0\n3\tYRS_RESIDENCE\t1\t0.0\t1.0\t7\n3\tYRS_RESIDENCE\t2\t1.0\t2.0\t17\n3\tYRS_RESIDENCE\t3\t2.0\t3.0\t48\n3\tYRS_RESIDENCE\t4\t3.0\t4.0\t58\n3\tYRS_RESIDENCE\t5\t4.0\t5.0\t39\n3\tYRS_RESIDENCE\t6\t5.0\t6.0\t8\n3\tYRS_RESIDENCE\t7\t6.0\t7.0\t8\n3\tYRS_RESIDENCE\t8\t7.0\t8.0\t4\n3\tYRS_RESIDENCE\t9\t8.0\t14.0\t1\n3\tYRS_RESIDENCE\t10\t\t\t0\n2\tYRS_RESIDENCE\t1\t0.0\t1.0\t317\n2\tYRS_RESIDENCE\t2\t1.0\t2.0\t608\n2\tYRS_RESIDENCE\t3\t2.0\t3.0\t913\n2\tYRS_RESIDENCE\t4\t3.0\t4.0\t879\n2\tYRS_RESIDENCE\t5\t4.0\t5.0\t718\n2\tYRS_RESIDENCE\t6\t5.0\t6.0\t465\n2\tYRS_RESIDENCE\t7\t6.0\t7.0\t223\n2\tYRS_RESIDENCE\t8\t7.0\t8.0\t97\n2\tYRS_RESIDENCE\t9\t8.0\t14.0\t90\n2\tYRS_RESIDENCE\t10\t\t\t0\n1\tYRS_RESIDENCE\t1\t0.0\t1.0\t324\n1\tYRS_RESIDENCE\t2\t1.0\t2.0\t625\n1\tYRS_RESIDENCE\t3\t2.0\t3.0\t961\n1\tYRS_RESIDENCE\t4\t3.0\t4.0\t937\n1\tYRS_RESIDENCE\t5\t4.0\t5.0\t757\n1\tYRS_RESIDENCE\t6\t5.0\t6.0\t473\n1\tYRS_RESIDENCE\t7\t6.0\t7.0\t231\n1\tYRS_RESIDENCE\t8\t7.0\t8.0\t101\n1\tYRS_RESIDENCE\t9\t8.0\t14.0\t91\n1\tYRS_RESIDENCE\t10\t\t\t0\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Cluster Rules","In the following example, the view shows attribute level details for the rule associated with each cluster id. ","For an attribute, support indicates the number of records that fall within the attribute range specified in the rule antecedent where the given attribute is not null. ","Confidence is a number between 0 and 1 that indicates how relevant this attribute is in distinguishing the records in the cluster from all the records in the whole data.","The larger the number, the more relevant the attribute."," ","The query below reverse-transforms the data to its original values, since the data used for building the model was normalized."],"enabled":true,"result":{"startTime":1715349107536,"interpreter":"md.low","endTime":1715349107598,"results":[{"message":"<h3 id=\"cluster-rules\">Cluster Rules<\/h3>\n<p>In the following example, the view shows attribute level details for the rule associated with each cluster id.\nFor an attribute, support indicates the number of records that fall within the attribute range specified in the rule antecedent where the given attribute is not null.\nConfidence is a number between 0 and 1 that indicates how relevant this attribute is in distinguishing the records in the cluster from all the records in the whole data.\nThe larger the number, the more relevant the attribute.<\/p>\n<p>The query below reverse-transforms the data to its original values, since the data used for building the model was normalized.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display rule details for leaf clusters","message":["%sql","","SELECT CLUSTER_ID, ATTRIBUTE_NAME, ATTRIBUTE_SUBNAME, OPERATOR,"," NUMERIC_VALUE, ATTRIBUTE_VALUE, SUPPORT, ROUND(CONFIDENCE,3) CONFIDENCE","FROM DM$VREM_SH_CLUS_SAMPLE_2","WHERE cluster_id IN (SELECT cluster_id"," FROM DM$VDEM_SH_CLUS_SAMPLE_2"," WHERE LEFT_CHILD_ID is NULL and RIGHT_CHILD_ID is NULL)","ORDER BY CLUSTER_ID, ATTRIBUTE_NAME, ATTRIBUTE_SUBNAME, OPERATOR, NUMERIC_VALUE, ATTRIBUTE_VALUE;"],"enabled":true,"result":{"startTime":1715349107678,"interpreter":"sql.low","endTime":1715349107827,"results":[{"message":"CLUSTER_ID\tATTRIBUTE_NAME\tATTRIBUTE_SUBNAME\tOPERATOR\tNUMERIC_VALUE\tATTRIBUTE_VALUE\tSUPPORT\tCONFIDENCE\n3\tAFFINITY_CARD\t\t<=\t1.0\t\t190\t0.079\n3\tAFFINITY_CARD\t\t>=\t0.0\t\t190\t0.079\n3\tCUST_CREDIT_LIMIT\t\t<=\t15000.0\t\t190\t0.001\n3\tCUST_CREDIT_LIMIT\t\t>=\t1500.0\t\t190\t0.001\n3\tCUST_GENDER\t\t=\t\tF\t190\t0.457\n3\tCUST_INCOME_LEVEL\t\tIN\t\tE: 90,000 - 109,999\t173\t0.006\n3\tCUST_INCOME_LEVEL\t\tIN\t\tF: 110,000 - 129,999\t173\t0.006\n3\tCUST_INCOME_LEVEL\t\tIN\t\tG: 130,000 - 149,999\t173\t0.006\n3\tCUST_INCOME_LEVEL\t\tIN\t\tH: 150,000 - 169,999\t173\t0.006\n3\tCUST_INCOME_LEVEL\t\tIN\t\tI: 170,000 - 189,999\t173\t0.006\n3\tCUST_INCOME_LEVEL\t\tIN\t\tJ: 190,000 - 249,999\t173\t0.006\n3\tCUST_INCOME_LEVEL\t\tIN\t\tK: 250,000 - 299,999\t173\t0.006\n3\tCUST_INCOME_LEVEL\t\tIN\t\tL: 300,000 and above\t173\t0.006\n3\tCUST_MARITAL_STATUS\t\t=\t\tMarried\t188\t0.125\n3\tCUST_YEAR_OF_BIRTH\t\t<=\t1978.7\t\t181\t0.045\n3\tCUST_YEAR_OF_BIRTH\t\t>\t1942.2\t\t181\t0.045\n3\tEDUCATION\t\tIN\t\t< Bach.\t167\t0.004\n3\tEDUCATION\t\tIN\t\tAssoc-A\t167\t0.004\n3\tEDUCATION\t\tIN\t\tAssoc-V\t167\t0.004\n3\tEDUCATION\t\tIN\t\tBach.\t167\t0.004\n3\tEDUCATION\t\tIN\t\tHS-grad\t167\t0.004\n3\tEDUCATION\t\tIN\t\tMasters\t167\t0.004\n3\tHOUSEHOLD_SIZE\t\t=\t\t4-5\t188\t0.321\n3\tOCCUPATION\t\tIN\t\t?\t174\t0.019\n3\tOCCUPATION\t\tIN\t\tCleric.\t174\t0.019\n3\tOCCUPATION\t\tIN\t\tExec.\t174\t0.019\n3\tOCCUPATION\t\tIN\t\tMachine\t174\t0.019\n3\tOCCUPATION\t\tIN\t\tOther\t174\t0.019\n3\tOCCUPATION\t\tIN\t\tProf.\t174\t0.019\n3\tOCCUPATION\t\tIN\t\tSales\t174\t0.019\n3\tOCCUPATION\t\tIN\t\tTechSup\t174\t0.019\n3\tYRS_RESIDENCE\t\t<=\t5.0\t\t162\t0.01\n3\tYRS_RESIDENCE\t\t>\t1.0\t\t162\t0.01\n3\tY_BOX_GAMES\t\t<=\t0.0\t\t186\t0.129\n3\tY_BOX_GAMES\t\t>=\t0.0\t\t186\t0.129\n5\tAFFINITY_CARD\t\t<=\t1.0\t\t1585\t0.051\n5\tAFFINITY_CARD\t\t>=\t0.0\t\t1585\t0.051\n5\tCUST_CREDIT_LIMIT\t\t<=\t15000.0\t\t1585\t0\n5\tCUST_CREDIT_LIMIT\t\t>=\t1500.0\t\t1585\t0\n5\tCUST_GENDER\t\t=\t\tM\t1585\t0.192\n5\tCUST_INCOME_LEVEL\t\tIN\t\tB: 30,000 - 49,999\t1432\t0.001\n5\tCUST_INCOME_LEVEL\t\tIN\t\tE: 90,000 - 109,999\t1432\t0.001\n5\tCUST_INCOME_LEVEL\t\tIN\t\tF: 110,000 - 129,999\t1432\t0.001\n5\tCUST_INCOME_LEVEL\t\tIN\t\tG: 130,000 - 149,999\t1432\t0.001\n5\tCUST_INCOME_LEVEL\t\tIN\t\tH: 150,000 - 169,999\t1432\t0.001\n5\tCUST_INCOME_LEVEL\t\tIN\t\tI: 170,000 - 189,999\t1432\t0.001\n5\tCUST_INCOME_LEVEL\t\tIN\t\tJ: 190,000 - 249,999\t1432\t0.001\n5\tCUST_INCOME_LEVEL\t\tIN\t\tK: 250,000 - 299,999\t1432\t0.001\n5\tCUST_INCOME_LEVEL\t\tIN\t\tL: 300,000 and above\t1432\t0.001\n5\tCUST_MARITAL_STATUS\t\t=\t\tMarried\t1583\t0.123\n5\tCUST_YEAR_OF_BIRTH\t\t<=\t1978.7\t\t1522\t0.041\n5\tCUST_YEAR_OF_BIRTH\t\t>\t1934.9\t\t1522\t0.041\n5\tEDUCATION\t\tIN\t\t< Bach.\t1405\t0.003\n5\tEDUCATION\t\tIN\t\tAssoc-A\t1405\t0.003\n5\tEDUCATION\t\tIN\t\tAssoc-V\t1405\t0.003\n5\tEDUCATION\t\tIN\t\tBach.\t1405\t0.003\n5\tEDUCATION\t\tIN\t\tHS-grad\t1405\t0.003\n5\tEDUCATION\t\tIN\t\tMasters\t1405\t0.003\n5\tEDUCATION\t\tIN\t\tProfsc\t1405\t0.003\n5\tHOUSEHOLD_SIZE\t\t=\t\t3\t1573\t0.147\n5\tOCCUPATION\t\tIN\t\t?\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tCleric.\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tCrafts\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tExec.\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tFarming\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tMachine\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tOther\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tProf.\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tSales\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tTechSup\t1514\t0.009\n5\tOCCUPATION\t\tIN\t\tTransp.\t1514\t0.009\n5\tYRS_RESIDENCE\t\t<=\t7.0\t\t1419\t0.031\n5\tYRS_RESIDENCE\t\t>\t2.0\t\t1419\t0.031\n5\tY_BOX_GAMES\t\t<=\t0.0\t\t1585\t0.177\n5\tY_BOX_GAMES\t\t>=\t0.0\t\t1585\t0.177\n6\tAFFINITY_CARD\t\t<=\t0.0\t\t1195\t0.027\n6\tAFFINITY_CARD\t\t>=\t0.0\t\t1195\t0.027\n6\tCUST_CREDIT_LIMIT\t\t<=\t15000.0\t\t1322\t0\n6\tCUST_CREDIT_LIMIT\t\t>=\t1500.0\t\t1322\t0\n6\tCUST_GENDER\t\tIN\t\tF\t1322\t0.039\n6\tCUST_GENDER\t\tIN\t\tM\t1322\t0.039\n6\tCUST_INCOME_LEVEL\t\tIN\t\tB: 30,000 - 49,999\t1201\t0\n6\tCUST_INCOME_LEVEL\t\tIN\t\tE: 90,000 - 109,999\t1201\t0\n6\tCUST_INCOME_LEVEL\t\tIN\t\tF: 110,000 - 129,999\t1201\t0\n6\tCUST_INCOME_LEVEL\t\tIN\t\tG: 130,000 - 149,999\t1201\t0\n6\tCUST_INCOME_LEVEL\t\tIN\t\tH: 150,000 - 169,999\t1201\t0\n6\tCUST_INCOME_LEVEL\t\tIN\t\tI: 170,000 - 189,999\t1201\t0\n6\tCUST_INCOME_LEVEL\t\tIN\t\tJ: 190,000 - 249,999\t1201\t0\n6\tCUST_INCOME_LEVEL\t\tIN\t\tK: 250,000 - 299,999\t1201\t0\n6\tCUST_INCOME_LEVEL\t\tIN\t\tL: 300,000 and above\t1201\t0\n6\tCUST_MARITAL_STATUS\t\tIN\t\tDivorc.\t1255\t0.11\n6\tCUST_MARITAL_STATUS\t\tIN\t\tNeverM\t1255\t0.11\n6\tCUST_MARITAL_STATUS\t\tIN\t\tSepar.\t1255\t0.11\n6\tCUST_MARITAL_STATUS\t\tIN\t\tWidowed\t1255\t0.11\n6\tCUST_YEAR_OF_BIRTH\t\t<=\t1978.7\t\t1270\t0.038\n6\tCUST_YEAR_OF_BIRTH\t\t>\t1934.9\t\t1270\t0.038\n6\tEDUCATION\t\tIN\t\t< Bach.\t1143\t0.001\n6\tEDUCATION\t\tIN\t\tAssoc-A\t1143\t0.001\n6\tEDUCATION\t\tIN\t\tAssoc-V\t1143\t0.001\n6\tEDUCATION\t\tIN\t\tBach.\t1143\t0.001\n6\tEDUCATION\t\tIN\t\tHS-grad\t1143\t0.001\n6\tEDUCATION\t\tIN\t\tMasters\t1143\t0.001\n6\tHOUSEHOLD_SIZE\t\tIN\t\t2\t1169\t0.12\n6\tHOUSEHOLD_SIZE\t\tIN\t\t9+\t1169\t0.12\n6\tOCCUPATION\t\tIN\t\t?\t1213\t0.002\n6\tOCCUPATION\t\tIN\t\tCleric.\t1213\t0.002\n6\tOCCUPATION\t\tIN\t\tCrafts\t1213\t0.002\n6\tOCCUPATION\t\tIN\t\tExec.\t1213\t0.002\n6\tOCCUPATION\t\tIN\t\tHandler\t1213\t0.002\n6\tOCCUPATION\t\tIN\t\tMachine\t1213\t0.002\n6\tOCCUPATION\t\tIN\t\tOther\t1213\t0.002\n6\tOCCUPATION\t\tIN\t\tProf.\t1213\t0.002\n6\tOCCUPATION\t\tIN\t\tSales\t1213\t0.002\n6\tOCCUPATION\t\tIN\t\tTechSup\t1213\t0.002\n6\tYRS_RESIDENCE\t\t<=\t6.0\t\t1102\t0.013\n6\tYRS_RESIDENCE\t\t>\t2.0\t\t1102\t0.013\n6\tY_BOX_GAMES\t\t<=\t0.0\t\t1322\t0.177\n6\tY_BOX_GAMES\t\t>=\t0.0\t\t1322\t0.177\n8\tAFFINITY_CARD\t\t<=\t0.0\t\t1149\t0.091\n8\tAFFINITY_CARD\t\t>=\t0.0\t\t1149\t0.091\n8\tCUST_CREDIT_LIMIT\t\t<=\t15000.0\t\t1170\t0.001\n8\tCUST_CREDIT_LIMIT\t\t>=\t1500.0\t\t1170\t0.001\n8\tCUST_GENDER\t\tIN\t\tF\t1170\t0.015\n8\tCUST_GENDER\t\tIN\t\tM\t1170\t0.015\n8\tCUST_INCOME_LEVEL\t\tIN\t\tB: 30,000 - 49,999\t1084\t0.002\n8\tCUST_INCOME_LEVEL\t\tIN\t\tE: 90,000 - 109,999\t1084\t0.002\n8\tCUST_INCOME_LEVEL\t\tIN\t\tF: 110,000 - 129,999\t1084\t0.002\n8\tCUST_INCOME_LEVEL\t\tIN\t\tG: 130,000 - 149,999\t1084\t0.002\n8\tCUST_INCOME_LEVEL\t\tIN\t\tH: 150,000 - 169,999\t1084\t0.002\n8\tCUST_INCOME_LEVEL\t\tIN\t\tI: 170,000 - 189,999\t1084\t0.002\n8\tCUST_INCOME_LEVEL\t\tIN\t\tJ: 190,000 - 249,999\t1084\t0.002\n8\tCUST_INCOME_LEVEL\t\tIN\t\tK: 250,000 - 299,999\t1084\t0.002\n8\tCUST_INCOME_LEVEL\t\tIN\t\tL: 300,000 and above\t1084\t0.002\n8\tCUST_MARITAL_STATUS\t\t=\t\tNeverM\t1041\t0.104\n8\tCUST_YEAR_OF_BIRTH\t\t<=\t1986.0\t\t1170\t0.135\n8\tCUST_YEAR_OF_BIRTH\t\t>\t1971.4\t\t1170\t0.135\n8\tEDUCATION\t\tIN\t\t10th\t1050\t0.008\n8\tEDUCATION\t\tIN\t\t11th\t1050\t0.008\n8\tEDUCATION\t\tIN\t\t< Bach.\t1050\t0.008\n8\tEDUCATION\t\tIN\t\tAssoc-V\t1050\t0.008\n8\tEDUCATION\t\tIN\t\tBach.\t1050\t0.008\n8\tEDUCATION\t\tIN\t\tHS-grad\t1050\t0.008\n8\tHOUSEHOLD_SIZE\t\tIN\t\t1\t1065\t0.11\n8\tHOUSEHOLD_SIZE\t\tIN\t\t2\t1065\t0.11\n8\tHOUSEHOLD_SIZE\t\tIN\t\t9+\t1065\t0.11\n8\tOCCUPATION\t\tIN\t\t?\t1103\t0.011\n8\tOCCUPATION\t\tIN\t\tCleric.\t1103\t0.011\n8\tOCCUPATION\t\tIN\t\tCrafts\t1103\t0.011\n8\tOCCUPATION\t\tIN\t\tExec.\t1103\t0.011\n8\tOCCUPATION\t\tIN\t\tHandler\t1103\t0.011\n8\tOCCUPATION\t\tIN\t\tMachine\t1103\t0.011\n8\tOCCUPATION\t\tIN\t\tOther\t1103\t0.011\n8\tOCCUPATION\t\tIN\t\tProf.\t1103\t0.011\n8\tOCCUPATION\t\tIN\t\tSales\t1103\t0.011\n8\tOCCUPATION\t\tIN\t\tTechSup\t1103\t0.011\n8\tYRS_RESIDENCE\t\t<=\t3.0\t\t1113\t0.093\n8\tYRS_RESIDENCE\t\t>=\t0.0\t\t1113\t0.093\n8\tY_BOX_GAMES\t\t<=\t1.0\t\t1169\t0.476\n8\tY_BOX_GAMES\t\t>\t0.0\t\t1169\t0.476\n9\tAFFINITY_CARD\t\t<=\t0.0\t\t194\t0.006\n9\tAFFINITY_CARD\t\t>=\t0.0\t\t194\t0.006\n9\tCUST_CREDIT_LIMIT\t\t<=\t15000.0\t\t233\t0.004\n9\tCUST_CREDIT_LIMIT\t\t>=\t1500.0\t\t233\t0.004\n9\tCUST_GENDER\t\t=\t\tM\t220\t0.098\n9\tCUST_INCOME_LEVEL\t\tIN\t\tB: 30,000 - 49,999\t225\t0.005\n9\tCUST_INCOME_LEVEL\t\tIN\t\tC: 50,000 - 69,999\t225\t0.005\n9\tCUST_INCOME_LEVEL\t\tIN\t\tE: 90,000 - 109,999\t225\t0.005\n9\tCUST_INCOME_LEVEL\t\tIN\t\tF: 110,000 - 129,999\t225\t0.005\n9\tCUST_INCOME_LEVEL\t\tIN\t\tG: 130,000 - 149,999\t225\t0.005\n9\tCUST_INCOME_LEVEL\t\tIN\t\tH: 150,000 - 169,999\t225\t0.005\n9\tCUST_INCOME_LEVEL\t\tIN\t\tI: 170,000 - 189,999\t225\t0.005\n9\tCUST_INCOME_LEVEL\t\tIN\t\tJ: 190,000 - 249,999\t225\t0.005\n9\tCUST_INCOME_LEVEL\t\tIN\t\tK: 250,000 - 299,999\t225\t0.005\n9\tCUST_INCOME_LEVEL\t\tIN\t\tL: 300,000 and above\t225\t0.005\n9\tCUST_MARITAL_STATUS\t\t=\t\tMarried\t232\t0.123\n9\tCUST_YEAR_OF_BIRTH\t\t<=\t1986.0\t\t233\t0.145\n9\tCUST_YEAR_OF_BIRTH\t\t>\t1971.4\t\t233\t0.145\n9\tEDUCATION\t\tIN\t\t11th\t201\t0.006\n9\tEDUCATION\t\tIN\t\t< Bach.\t201\t0.006\n9\tEDUCATION\t\tIN\t\tAssoc-V\t201\t0.006\n9\tEDUCATION\t\tIN\t\tBach.\t201\t0.006\n9\tEDUCATION\t\tIN\t\tHS-grad\t201\t0.006\n9\tHOUSEHOLD_SIZE\t\t=\t\t3\t214\t0.12\n9\tOCCUPATION\t\tIN\t\tCleric.\t211\t0.016\n9\tOCCUPATION\t\tIN\t\tCrafts\t211\t0.016\n9\tOCCUPATION\t\tIN\t\tExec.\t211\t0.016\n9\tOCCUPATION\t\tIN\t\tHandler\t211\t0.016\n9\tOCCUPATION\t\tIN\t\tMachine\t211\t0.016\n9\tOCCUPATION\t\tIN\t\tProf.\t211\t0.016\n9\tOCCUPATION\t\tIN\t\tProtec.\t211\t0.016\n9\tOCCUPATION\t\tIN\t\tSales\t211\t0.016\n9\tOCCUPATION\t\tIN\t\tTransp.\t211\t0.016\n9\tYRS_RESIDENCE\t\t<=\t4.0\t\t218\t0.067\n9\tYRS_RESIDENCE\t\t>\t1.0\t\t218\t0.067\n9\tY_BOX_GAMES\t\t<=\t1.0\t\t233\t0.48\n9\tY_BOX_GAMES\t\t>\t0.0\t\t233\t0.48\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"bar","title":"Apply model to CUSTOMERS360_V and display customer count per cluster","message":["%sql","","-- For a descriptive machine learning technique like Clustering, \"scoring\" involves assigning each record to a cluster with a certain probability. ","-- Hover over bars in the bar chart to view the size of each cluster. ","","SELECT CLUSTER_ID(EM_SH_CLUS_SAMPLE_2 USING *) AS CLUSTER_ID, COUNT(*) AS CNT ","FROM CUSTOMERS360_V","GROUP BY CLUSTER_ID(EM_SH_CLUS_SAMPLE_2 USING *)","ORDER BY CNT DESC;"],"enabled":true,"result":{"startTime":1715349107920,"interpreter":"sql.low","endTime":1715349108044,"results":[{"message":"CLUSTER_ID\tCNT\n5\t1585\n6\t1322\n8\t1170\n9\t233\n3\t190\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"List ten customers most likely in cluster 6","message":["%sql","","SELECT CUST_ID, "," ROUND(CLUSTER_PROBABILITY(EM_SH_CLUS_SAMPLE_2, 6 USING *),4) PROB","FROM CUSTOMERS360_V","ORDER BY PROB DESC","FETCH FIRST 10 ROWS ONLY"],"enabled":true,"result":{"startTime":1715349108125,"interpreter":"sql.low","endTime":1715349108250,"results":[{"message":"CUST_ID\tPROB\n100134\t1\n102893\t1\n101392\t1\n101297\t1\n102828\t1\n102740\t1\n100804\t1\n103791\t1\n103948\t1\n103829\t1\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":5,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"List most relevant predictors and their values for specific customers cluster assignments","message":["%sql","","-- For customer id, pick among 101362, 102087 or 100456. These represent customers ","-- with > 20% likelihood of assignment to their cluster.","","SELECT CUST_ID,"," CLUSTER_ID,"," ROUND(PROB*100,2) PROB_PCT,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute1\",17,100)),'rank=\"1\"/>') FIRST_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute2\",17,100)),'rank=\"2\"/>') SECOND_ATTRIBUTE","FROM (SELECT CUST_ID, S.CLUSTER_ID, PROBABILITY PROB, "," CLUSTER_DETAILS(EM_SH_CLUS_SAMPLE_2 USING T.*) DETAIL"," FROM (SELECT V.*, CLUSTER_SET(EM_SH_CLUS_SAMPLE_2, NULL, 0.2 USING *) PSET"," FROM CUSTOMERS360_V V"," WHERE cust_id = ${CUST_ID ='101362','101362'|'100456'}) T,"," TABLE(T.PSET) S"," ORDER BY 2 DESC) OUT,"," XMLTABLE('/Details'"," PASSING OUT.DETAIL"," COLUMNS "," \"Attribute1\" XMLType PATH 'Attribute[1]',"," \"Attribute2\" XMLType PATH 'Attribute[2]') OUTPRED;"],"enabled":true,"result":{"startTime":1715349108329,"interpreter":"sql.low","endTime":1715349108606,"results":[{"message":"CUST_ID\tCLUSTER_ID\tPROB_PCT\tFIRST_ATTRIBUTE\tSECOND_ATTRIBUTE\n100456\t6\t100\t\"CUST_MARITAL_STATUS\" actualValue=\"Divorc.\" weight=\".001\" \t\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":"{\"CUST_ID\":\"'100456'\"}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[{\"type\":\"Select\",\"name\":\"CUST_ID\",\"displayName\":null,\"defaultValue\":\"'101362'\",\"argument\":null,\"options\":[{\"value\":\"'101362'\",\"displayName\":null},{\"value\":\"'100456'\",\"displayName\":null}],\"isHidden\":false,\"isProgrammatic\":false}]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## Alternative approach for building clustering model using an analytic function","In addition to creating a persistent model that is stored as a schema object, models can be built and scored on data dynamically using Oracle's analytic function syntax.","","In the following example, we segment customers into 4 groups based on common characteristics and provide the segment assignments. Note that this query does not reference a pre-build clustering model, but rather it segments the input data on the fly. Rerunning the same query with different input will result in a different segmentation. It also provides the main reasons (attributes) why a given customer is placed into a specific cluster.","","Note that the where clause has to be placed outside of the inline view so that the analytic function will build the clustering model on all the data, and not just the selected customers."],"enabled":true,"result":{"startTime":1715349108686,"interpreter":"md.low","endTime":1715349108747,"results":[{"message":"<h2 id=\"alternative-approach-for-building-clustering-model-using-an-analytic-function\">Alternative approach for building clustering model using an analytic function<\/h2>\n<p>In addition to creating a persistent model that is stored as a schema object, models can be built and scored on data dynamically using Oracle's analytic function syntax.<\/p>\n<p>In the following example, we segment customers into 4 groups based on common characteristics and provide the segment assignments. Note that this query does not reference a pre-build clustering model, but rather it segments the input data on the fly. Rerunning the same query with different input will result in a different segmentation. It also provides the main reasons (attributes) why a given customer is placed into a specific cluster.<\/p>\n<p>Note that the where clause has to be placed outside of the inline view so that the analytic function will build the clustering model on all the data, and not just the selected customers.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Build and apply a transient model using analytic functions","message":["%sql","","SELECT CUST_ID,"," CLUSTER_ID,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute1\",17,100)),'rank=\"1\"/>') FIRST_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute2\",17,100)),'rank=\"2\"/>') SECOND_ATTRIBUTE,"," RTRIM(TRIM(SUBSTR(OUTPRED.\"Attribute3\",17,100)),'rank=\"3\"/>') THIRD_ATTRIBUTE","FROM (SELECT * "," FROM (SELECT CUST_ID,"," CLUSTER_ID(INTO 4 USING CUST_GENDER, CUST_MARITAL_STATUS, CUST_YEAR_OF_BIRTH, CUST_INCOME_LEVEL, "," CUST_CREDIT_LIMIT, EDUCATION, AFFINITY_CARD, HOUSEHOLD_SIZE, OCCUPATION,"," YRS_RESIDENCE, Y_BOX_GAMES) OVER () CLUSTER_ID,"," CLUSTER_DETAILS(INTO 4 USING CUST_GENDER, CUST_MARITAL_STATUS, CUST_YEAR_OF_BIRTH, CUST_INCOME_LEVEL, "," CUST_CREDIT_LIMIT, EDUCATION, AFFINITY_CARD, HOUSEHOLD_SIZE, OCCUPATION,"," YRS_RESIDENCE, Y_BOX_GAMES) OVER () CLUSTER_DET"," FROM CUSTOMERS360_V)"," ORDER By 1) OUT,"," XMLTABLE('/Details'"," PASSING OUT.CLUSTER_DET"," COLUMNS "," \"Attribute1\" XMLType PATH 'Attribute[1]',"," \"Attribute2\" XMLType PATH 'Attribute[2]',"," \"Attribute3\" XMLType PATH 'Attribute[3]') OUTPRED","FETCH FIRST 10 ROWS ONLY;"," "],"enabled":true,"result":{"startTime":1715349108826,"interpreter":"sql.low","endTime":1715349109101,"results":[{"message":"CUST_ID\tCLUSTER_ID\tFIRST_ATTRIBUTE\tSECOND_ATTRIBUTE\tTHIRD_ATTRIBUTE\n100001\t3\t\"CUST_YEAR_OF_BIRTH\" actualValue=\"1941\" weight=\".059\" \t\"CUST_CREDIT_LIMIT\" actualValue=\"1500\" weight=\".038\" \t\"AFFINITY_CARD\" actualValue=\"0\" weight=\".015\" \n100002\t4\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".025\" \t\"CUST_GENDER\" actualValue=\"F\" weight=\".024\" \t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\".019\" \n100003\t4\t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".025\" \t\"CUST_MARITAL_STATUS\" actualValue=\"NeverM\" weight=\".018\" \t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\".016\" \n100004\t4\t\"CUST_CREDIT_LIMIT\" actualValue=\"15000\" weight=\".038\" \t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".028\" \t\"CUST_MARITAL_STATUS\" actualValue=\"Divorc.\" weight=\".028\" \n100005\t1\t\"AFFINITY_CARD\" actualValue=\"1\" weight=\".157\" \t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\".009\" \t\"YRS_RESIDENCE\" actualValue=\"5\" weight=\".009\" \n100006\t2\t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\".131\" \t\"CUST_YEAR_OF_BIRTH\" actualValue=\"1983\" weight=\".086\" \t\"YRS_RESIDENCE\" actualValue=\"2\" weight=\".056\" \n100007\t4\t\"CUST_MARITAL_STATUS\" actualValue=\"Divorc.\" weight=\".027\" \t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".026\" \t\"CUST_GENDER\" actualValue=\"F\" weight=\".026\" \n100008\t4\t\"CUST_CREDIT_LIMIT\" actualValue=\"15000\" weight=\".035\" \t\"HOUSEHOLD_SIZE\" actualValue=\"2\" weight=\".026\" \t\"Y_BOX_GAMES\" actualValue=\"0\" weight=\".019\" \n100009\t1\t\"AFFINITY_CARD\" actualValue=\"1\" weight=\".137\" \t\"EDUCATION\" actualValue=\"Bach.\" weight=\".01\" \t\"OCCUPATION\" actualValue=\"Prof.\" weight=\".007\" \n100010\t2\t\"Y_BOX_GAMES\" actualValue=\"1\" weight=\".115\" \t\"CUST_YEAR_OF_BIRTH\" actualValue=\"1975\" weight=\".046\" \t\"YRS_RESIDENCE\" actualValue=\"3\" weight=\".028\" \n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## Alternative approach to creating clustering model using a settings table","","The settings table is an alternative way to specify algorithm settings to build the model. We first drop the table, create it, and then populate it with settings before building the model. "],"enabled":true,"result":{"startTime":1715349109183,"interpreter":"md.low","endTime":1715349109246,"results":[{"message":"<h2 id=\"alternative-approach-to-creating-clustering-model-using-a-settings-table\">Alternative approach to creating clustering model using a settings table<\/h2>\n<p>The settings table is an alternative way to specify algorithm settings to build the model. We first drop the table, create it, and then populate it with settings before building the model.<\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Create and populate model settings table","message":["%script ","","BEGIN EXECUTE IMMEDIATE 'DROP TABLE EM_SH_SAMPLE_SETTINGS PURGE';","EXCEPTION WHEN OTHERS THEN NULL; END;","/","CREATE TABLE EM_SH_SAMPLE_SETTINGS (SETTING_NAME VARCHAR2(30),"," SETTING_VALUE VARCHAR2(4000));","","BEGIN "," INSERT INTO EM_SH_SAMPLE_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.ALGO_NAME, DBMS_DATA_MINING.ALGO_EXPECTATION_MAXIMIZATION);"," INSERT INTO EM_SH_SAMPLE_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.EMCS_MODEL_SEARCH, DBMS_DATA_MINING.EMCS_MODEL_SEARCH_ENABLE);"," INSERT INTO EM_SH_SAMPLE_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.PREP_AUTO, DBMS_DATA_MINING.PREP_AUTO_ON);"," INSERT INTO EM_SH_SAMPLE_SETTINGS (SETTING_NAME, SETTING_VALUE) VALUES (DBMS_DATA_MINING.EMCS_CLUSTER_STATISTICS, DBMS_DATA_MINING.EMCS_CLUS_STATS_ENABLE);","END;"],"enabled":true,"result":{"startTime":1715349109325,"interpreter":"script.low","endTime":1715349109550,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nTable EM_SH_SAMPLE_SETTINGS created.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"View the contents of the model settings table","message":["%sql","-- NOTE: The settings table can be reused or modified in multiple model builds. ","","SELECT * FROM EM_SH_SAMPLE_SETTINGS;"],"enabled":true,"result":{"startTime":1715349109628,"interpreter":"sql.low","endTime":1715349109704,"results":[{"message":"SETTING_NAME\tSETTING_VALUE\nALGO_NAME\tALGO_EXPECTATION_MAXIMIZATION\nEMCS_MODEL_SEARCH\tEMCS_MODEL_SEARCH_ENABLE\nPREP_AUTO\tON\nEMCS_CLUSTER_STATISTICS\tEMCS_CLUS_STATS_ENABLE\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Drop, then build the EM Clustering model","message":["%script","","BEGIN DBMS_DATA_MINING.DROP_MODEL('EM_SH_CLUS_SAMPLE_3');","EXCEPTION WHEN OTHERS THEN NULL; END;","/","","BEGIN","DBMS_DATA_MINING.CREATE_MODEL("," MODEL_NAME => 'EM_SH_CLUS_SAMPLE_3',"," MINING_FUNCTION => DBMS_DATA_MINING.CLUSTERING,"," DATA_TABLE_NAME => 'CUSTOMERS360_V',"," CASE_ID_COLUMN_NAME => 'CUST_ID',"," SETTINGS_TABLE_NAME => 'EM_SH_SAMPLE_SETTINGS');","END;"],"enabled":true,"result":{"startTime":1715349109784,"interpreter":"script.low","endTime":1715349116788,"results":[{"message":"\nPL/SQL procedure successfully completed.\n\n\n---------------------------\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"Display the actual settings used by the algorithm","message":["%sql","","SELECT SETTING_NAME, SETTING_VALUE","FROM USER_MINING_MODEL_SETTINGS","WHERE MODEL_NAME = 'EM_SH_CLUS_SAMPLE_3'","ORDER BY SETTING_NAME;"],"enabled":true,"result":{"startTime":1715349117034,"interpreter":"sql.low","endTime":1715349117112,"results":[{"message":"SETTING_NAME\tSETTING_VALUE\nALGO_NAME\tALGO_EXPECTATION_MAXIMIZATION\nCLUS_NUM_CLUSTERS\t10\nEMCS_CLUSTER_COMPONENTS\tEMCS_CLUSTER_COMP_ENABLE\nEMCS_CLUSTER_STATISTICS\tEMCS_CLUS_STATS_ENABLE\nEMCS_CLUSTER_THRESH\t2\nEMCS_LINKAGE_FUNCTION\tEMCS_LINKAGE_SINGLE\nEMCS_LOGLIKE_IMPROVEMENT\t.001\nEMCS_MAX_NUM_ATTR_2D\t50\nEMCS_MIN_PCT_ATTR_SUPPORT\t.1\nEMCS_MODEL_SEARCH\tEMCS_MODEL_SEARCH_ENABLE\nEMCS_NUM_COMPONENTS\t20\nEMCS_NUM_DISTRIBUTION\tEMCS_NUM_DISTR_SYSTEM\nEMCS_NUM_EQUIWIDTH_BINS\t11\nEMCS_NUM_ITERATIONS\t100\nEMCS_NUM_PROJECTIONS\t50\nEMCS_RANDOM_SEED\t0\nEMCS_REMOVE_COMPONENTS\tEMCS_REMOVE_COMPS_ENABLE\nODMS_DETAILS\tODMS_ENABLE\nODMS_MISSING_VALUE_TREATMENT\tODMS_MISSING_VALUE_AUTO\nODMS_SAMPLING\tODMS_SAMPLING_DISABLE\nPREP_AUTO\tON\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":6,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","## End of Script"],"enabled":true,"result":{"startTime":1715349117203,"interpreter":"md.low","endTime":1715349117273,"results":[{"message":"<h2 id=\"end-of-script\">End of Script<\/h2>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md"],"enabled":true,"result":{"startTime":1715349117362,"interpreter":"md.low","endTime":1715349117426,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":true,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"}],"version":"6","snapshot":false,"tags":null}]